package com.ruge.test.commons.dbutils;

import com.alibaba.druid.support.json.JSONUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 爱丽丝、如歌  创建于 2018/6/15 20:14
 * 说明:  commons-dbutils 查询工具类
 */
public class Query {
	/**
	 * 返回对象list集合
	 * ①ArrayHandler：     将查询结果的第一行数据，保存到Object数组中
	 * ②ArrayListHandler     将查询的结果，每一行先封装到Object数组中，然后将数据存入List集合
	 * ③BeanHandler     将查询结果的第一行数据，封装到user对象
	 * ④BeanListHandler     将查询结果的每一行封装到user对象，然后再存入List集合
	 * ⑤ColumnListHandler     将查询结果的指定列的数据封装到List集合中
	 * ⑥MapHandler     将查询结果的第一行数据封装到map结合（key==列名，value==列值）
	 * ⑦MapListHandler     将查询结果的每一行封装到map集合（key==列名，value==列值），再将map集合存入List集合
	 * ⑧BeanMapHandler     将查询结果的每一行数据，封装到User对象，再存入mao集合中（key==列名，value==列值）
	 * ⑨KeyedHandler     将查询的结果的每一行数据，封装到map1（key==列名，value==列值 ），然后将map1集合（有多个）存入map2集合（只有一个）
	 * ⑩ScalarHandler     封装类似count、avg、max、min、sum......函数的执行结果
	 */
	@Test
	public void arrayHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		Object[] query = runner.query(conn, "select * from sys_user where 1=1 " + sqlWhere, new ArrayHandler());
		for (Object s : query
		) {
			System.out.print(s);
		}
		System.out.println();
		DbUtils.close(conn);
	}

	@Test
	public void arrayListHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		List<Object[]> query = runner.query(conn, "select * from sys_user where 1=1 " + sqlWhere, new ArrayListHandler());
		for(int i=0;i<query.size();i++){
			for (Object s : query.get(i)
			) {
				System.out.print(s  + " ");
			}
		System.out.println();
		}

		DbUtils.close(conn);
	}

	@Test
	public void beanHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		Object[] params = {};
		String sql = "select * from sys_user";
		SysUser query = runner.query(conn, sql, new BeanHandler<SysUser>(SysUser.class), params);
		System.out.println(query);
		DbUtils.close(conn);
	}

	@Test
	public void beanListHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		List<SysUser> query = runner.query(conn, "select * from sys_user where 1=1 " + sqlWhere, new BeanListHandler<SysUser>(SysUser.class));
		for (SysUser s : query
		) {
			System.out.println(s);
		}
		DbUtils.close(conn);
	}

	@Test
	public void columnListHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		List<Object> query = runner.query(conn, "select * from sys_user where 1=1 " + sqlWhere, new ColumnListHandler<>());
		for (Object s : query
		) {
			System.out.println(s);
		}
		DbUtils.close(conn);
	}

	@Test
	public void mapHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		Map<String, Object> query = runner.query(conn, "select * from sys_user where 1=1 " + sqlWhere, new MapHandler());
		for (Object in : query.keySet()) {
			//map.keySet()返回的是所有key的值
			String str = query.get(in).toString();//得到每个key多对用value的值
			System.out.print(in + " " + str + ":");
		}
		System.out.println();
		DbUtils.close(conn);
	}

	@Test
	public void mapListHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		List<Map<String, Object>> query = runner.query(conn, "select * from sys_user where 1=1 " + sqlWhere, new MapListHandler());
		//System.out.println(query);
		for (Map<String, Object> map : query) {
			for (Object in : map.keySet()) {
				//map.keySet()返回的是所有key的值
				String str = map.get(in).toString();//得到每个key多对用value的值
				//System.out.print(in + ":" + str + ",");
			}
			//	System.out.println();
		}
		String s = JSONUtils.toJSONString(query);

		System.out.println(s);


		DbUtils.close(conn);
	}

	@Test
	public void beanMapHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		Object query = runner.query(conn, "select * from sys_user where 1=1 " + sqlWhere, new BeanMapHandler(SysUser.class));
		System.out.println(query);
		DbUtils.close(conn);
	}

	@Test
	public void keyedHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		Object query = runner.query(conn, "select * from sys_user where 1=1 " + sqlWhere, new KeyedHandler());
		System.out.println(query);
		DbUtils.close(conn);
	}

	@Test
	public void scalarHandler() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner runner = new QueryRunner();
		String sqlWhere = "";
		Object query = runner.query(conn, "select count(1) from sys_user where 1=1 " + sqlWhere, new ScalarHandler());
		System.out.println(query);
		DbUtils.close(conn);
	}
}
